package mall.dao;

import com.alibaba.druid.util.StringUtils;
import mall.model.bo.ChangeOrderBO;
import mall.model.bo.PageOrderBO;
import mall.model.vo.OrderInfoVO;
import mall.model.vo.PageOrderInfoVO;
import mall.model.vo.SpecVO;
import mall.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/*
 *
 * @author Yonni
 * @version 1.8.0_201
 * @date 2020-05-30 09:11
 *
 * */
public class OrderDaoImpl implements OrderDao {

    /*
    * 分页查询的sql语句
    *   select * from orders where ... limit ? offset ?
    * */
    @Override
    public List<PageOrderInfoVO> ordersByPage(PageOrderBO orderBO) {
        Map<String, Object> results = getDynamicSql(orderBO);
        String sql = (String) results.get("sql");
        List<Object> params = (List<Object>) results.get("params");
        String prefix_sql = "select id,userId,goodsDetailId, goods, spec, num as goodsNum, amount, stateId, nickname, name, address, phone ";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        params.add(orderBO.getPagesize());
        params.add((orderBO.getCurrentPage() - 1) * orderBO.getPagesize());
        List<PageOrderInfoVO> orderInfoVOS = null;
        try {
            orderInfoVOS = queryRunner.query(prefix_sql + sql + " limit ? offset ?", new BeanListHandler<PageOrderInfoVO>(PageOrderInfoVO.class), params.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return orderInfoVOS;
    }

    /*
     * select count(id) from orders where ...
     * */
    @Override
    public int getTotalCounts(PageOrderBO orderBO) {
        Map<String, Object> results = getDynamicSql(orderBO);
        String sql = (String) results.get("sql");
        List<Object> params = (List<Object>) results.get("params");
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String prefix_sql = "select count(id) ";
        Long query = null;
        try {
            query = (Long) queryRunner.query(prefix_sql + sql, new ScalarHandler<>(), params.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return query.intValue();
    }

    @Override
    public Integer getGoodsId(String id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        Integer query = null;
        try {
            query = queryRunner.query("select goodsId from orders where id = ?",
                    new ScalarHandler<Integer>(), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return query.intValue();
    }

    @Override
    public List<SpecVO> getSpecVOS(int goodsId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<SpecVO> query = null;
        try {
            query = queryRunner.query("select id, specName, unitPrice from spec where goodsId = ?", new BeanListHandler<SpecVO>(SpecVO.class), goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    @Override
    public OrderInfoVO order(String id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        OrderInfoVO query = null;
        try {
            query = queryRunner.query("select id, amount, num, goodsDetailId, stateId as state, goods from orders where id = ?",
                    new BeanHandler<OrderInfoVO>(OrderInfoVO.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    @Override
    public void changeOrder(ChangeOrderBO changeOrderBO) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("update orders set stateId = ?, goodsDetailId = ?, num = ? where id = ?",
                    changeOrderBO.getState(),
                    changeOrderBO.getSpec(),
                    changeOrderBO.getNum(),
                    changeOrderBO.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteOrder(String id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("delete from orders where id = ?", id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private Map<String, Object> getDynamicSql(PageOrderBO orderBO) {
        String base = " from orders where 1 = 1 ";//注意最后要有一个空格
        ArrayList<Object> list = new ArrayList<>();
        if (orderBO.getState() != -1) {
            base = base + " and stateId = ?";//注意一开始的空格
            list.add(orderBO.getState());
        }
        if (!StringUtils.isEmpty(orderBO.getMoneyLimit1())) {
            base = base + " and amount <= ?";
            list.add(Double.parseDouble(orderBO.getMoneyLimit1()));
        }
        if (!StringUtils.isEmpty(orderBO.getMoneyLimit2())) {
            base = base + " and amount >= ?";
            list.add(Double.parseDouble(orderBO.getMoneyLimit2()));
        }
        if (!StringUtils.isEmpty(orderBO.getGoods())) {
            base = base + " and goods like ?";
            list.add("%" + orderBO.getGoods() + "%");
        }
        if (!StringUtils.isEmpty(orderBO.getAddress())) {
            base = base + " and address like ?";
            list.add("%" + orderBO.getAddress() + "%");
        }
        if (!StringUtils.isEmpty(orderBO.getName())) {
            base = base + " and name like ?";
            list.add("%" + orderBO.getName() + "%");
        }
        if (!StringUtils.isEmpty(orderBO.getId())) {
            base = base + " and id = ?";
            list.add(Integer.parseInt(orderBO.getId()));
        }
        Map<String, Object> map = new HashMap<>();
        map.put("sql", base);
        map.put("params", list);

        return map;
    }
}
